
package com.hex.ds.em.hcdp.gauss.pack.service.impl;

import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.io.resource.Resource;
import cn.hutool.core.util.StrUtil;
import com.hex.ds.em.hcdp.gauss.common.entity.ScriptPackScanResultEntity;
import com.hex.ds.em.hcdp.gauss.common.utils.FileUtil;
import com.hex.ds.em.hcdp.gauss.common.utils.PoiUtil;
import com.hex.ds.em.hcdp.gauss.common.utils.SqlFormatterUtil;
import com.hex.ds.em.hcdp.gauss.pack.dao.IScanItemRepository;
import com.hex.ds.em.hcdp.gauss.pack.entity.ScriptItemEntity;
import com.hex.ds.em.hcdp.gauss.pack.entity.ScriptPackScanListEntity;
import com.hex.ds.em.hcdp.gauss.pack.service.IScriptPackTaskService;
import com.hex.hlrt.gauss.g4.basic.gen.GaussLexer;
import com.hex.hlrt.gauss.g4.basic.gen.GaussParser;
import com.hex.hlrt.gauss.g4.basic.gen.GaussParserListener;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.antlr.v4.runtime.CharStreams;
import org.antlr.v4.runtime.CommonTokenStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFChart;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

/**
 * 扫描业务代码
 *
 * @author ky.zhang
 */
@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class ScriptPackTaskServiceImpl implements IScriptPackTaskService {

    private final IScanItemRepository scanItemRepository;

    private final List<GaussParserListener> parserListenerList;

    @Value("${script.pack.gaussPath}")
    private String gaussFilePaths;

    @Value("${script.pack.scanDispositionPath}")
    private String scanExcelPath;

    @Value("${script.pack.reportPath}")
    private String reportPath;

    @Value("${script.pack.sqlScanResultPath}")
    private String sqlScanResultPath;

    //脚本数量
    private static Integer fileNum;

    //失败的脚本数量
    private static Integer fileFailNum;

    //脚本失败次数<文件名，失败次数>
    private final Map<String, Integer> fileFailNumMap = new HashMap<>();

    //性能类隐患类失败次数
    private final Map<String, Integer> perfAndRiskFailMap = new HashMap<>();

    //失败的性能类和隐患类
    private final List<String> perfAndRiskFailList = new ArrayList<>();

    //失败描述
    private List<String> perfAndRiskFailTopTenList;

    //问题分类统计（性能类、隐患类等等问题个数）
    private final Map<String, Integer> failTypeMap = new HashMap<>();

    //文件分类问题(Map<文件名,Map<扫描类别,出错次数>>)
    private final Map<String, Map<String, Integer>> fileFailTypeMap = new HashMap<>();

    // 指定时区为上海
    ZoneId zoneId = ZoneId.of("Asia/Shanghai");

    public static Map<String, ScriptPackScanResultEntity> wordResult = new HashMap<>();

    /**
     * 扫描业务代码
     */
    @Override
    public void scanFile() throws IOException, InvalidFormatException {
        //执行前先清空扫描结果目录
        clearDirectory();
        //读取扫描项配置并存入数据库
        List<ScriptItemEntity> scriptItemEntityList = readScanExcel(scanExcelPath);
        //校验目录是否符合格式
        // checkDirectory(scriptFilePath);
        //获取脚本数量
        fileNum = countDirectory(gaussFilePaths);
        //读取 gauss 目录脚本
        Map<String, String> filePathMap = FileUtil.getFilePaths(gaussFilePaths);
        //创建文件对象
        Map<String, File> fileObjectMap = createFileObjectMap(filePathMap);
        //读取文件信息并存入数据库
//        readFileName(fileObjectMap);
        //读取文件内的sql并扫描
        readFileMapContent(fileObjectMap, scriptItemEntityList);
        //失败描述top10
        getFailList();
        //结果导出到word
        exportWord(failTypeMap);
    }

    private void clearDirectory() {
        // 创建File对象
        File directory = new File(sqlScanResultPath);

        // 检查目录是否存在以及是否为目录
        if (directory.exists() && directory.isDirectory()) {
            // 获取目录中的所有文件
            File[] files = directory.listFiles();

            if (files != null) {
                // 遍历文件并删除它们
                for (File file : files) {
                    if (file.isFile()) {
                        boolean deleted = file.delete();
                        if (deleted) {
                            log.info("Deleted file: " + file.getName());
                        } else {
                            log.error("Failed to delete file: " + file.getName());
                        }
                    }
                }
            } else {
                log.info("结果目录已为空");
            }
        } else {
            log.info("结果目录不存在");
        }
    }

    private void getFailList() {
        perfAndRiskFailTopTenList = perfAndRiskFailMap.entrySet()
                .stream()
                .sorted(Map.Entry.<String, Integer>comparingByValue().reversed())
                .map(Map.Entry::getKey)
                .limit(10)
                .collect(Collectors.toList());
    }


    private void getFailFileNum() {
        int failFileNum = 0;
        for (Map.Entry<String, Integer> ignored : fileFailNumMap.entrySet()) {
            failFileNum++;
        }
        fileFailNum = failFileNum;
    }

    /**
     * 数据导出到word
     */
    private void exportWord(Map<String, Integer> totalMap) throws IOException, InvalidFormatException {
        //读取模板word
        Resource resource = new ClassPathResource("Template.docx");
        InputStream stream = resource.getStream();
        // 创建一个Word文档
        XWPFDocument document = new XWPFDocument(stream);
        exportChart(document, totalMap);
        // 定义导出文件的路径和名称
        String fileName = "XX客户大数据任务代码扫描巡检报告.docx";
        String reportDir = reportPath;
        reportPath = reportDir + File.separator + fileName;

        // 将文档写入文件
        try (FileOutputStream out = new FileOutputStream(reportPath)) {
            document.write(out);
            System.out.println("文档已成功导出到: " + reportPath);
        } catch (IOException e) {
            throw new RuntimeException("word导出失败", e);
        } finally {
            try {
                document.close();
            } catch (IOException e) {
                log.error("word导出失败");
            }
        }
    }

    /**
     * 具体数据刷新到doc对象
     *
     * @param doc word文件对象
     */
    private void exportChart(XWPFDocument doc, Map<String, Integer> totalMap) throws IOException, InvalidFormatException {
        List<XWPFChart> charts = doc.getCharts();// 获取模版中所有的图表
        int functionNum = 0;
        int performanceNum = 0;
        int hiddenNum = 0;
        int specificationNum = 0;
        for (int i = 0; i < charts.size(); i++) {
            if (i == 0) {
                XSSFWorkbook workbook = charts.get(i).getWorkbook();
                XSSFSheet sheet = workbook.getSheetAt(0);

                //系列信息
                String[] seriesNames = {""};
                //分类信息
                sheet.getRow(1).getCell(0).setCellValue("Gauss");
                //设置数值
                sheet.getRow(1).getCell(1).setCellValue(fileNum);
                //设置标题
                String title = "脚本扫描总数" + (fileNum);
                PoiUtil.wordExportChar(charts.get(i), title, seriesNames, sheet, 1, 2);
            } else if (i == 1) {
                XSSFWorkbook workbook = charts.get(i).getWorkbook();
                XSSFSheet sheet = workbook.getSheetAt(0);
                //系列信息
                String[] seriesNames = {"脚本扫描情况"};
                //分类信息
                sheet.getRow(1).getCell(0).setCellValue("功能类");
                sheet.getRow(2).getCell(0).setCellValue("规范类");
                sheet.getRow(3).getCell(0).setCellValue("性能类");
                sheet.getRow(4).getCell(0).setCellValue("隐患类");
                //设置数值
                for (Map.Entry<String, Integer> entry : totalMap.entrySet()) {
                    String type = entry.getKey();
                    if (type.equals("功能类")) functionNum = entry.getValue();
                    if (type.equals("规范类")) specificationNum = entry.getValue();
                    if (type.equals("性能类")) performanceNum = entry.getValue();
                    if (type.equals("隐患类")) hiddenNum = entry.getValue();
                }
                sheet.getRow(1).getCell(1).setCellValue(functionNum);
                sheet.getRow(2).getCell(1).setCellValue(specificationNum);
                sheet.getRow(3).getCell(1).setCellValue(performanceNum);
                sheet.getRow(4).getCell(1).setCellValue(hiddenNum);
                //设置标题
                String title = "总计" + fileFailNum + "个Gauss任务脚本存在代码问题";
                PoiUtil.wordExportChar(charts.get(i), title, seriesNames, sheet, 1, 4);
            }
        }
        //修改word模板内容
        Integer total = functionNum + specificationNum + performanceNum + hiddenNum;
        PoiUtil.replaceTextInDocument(doc, "one", fileNum.toString());
        PoiUtil.replaceTextInDocument(doc, "two", fileFailNum.toString());
        PoiUtil.replaceTextInDocument(doc, "three", getNum(fileFailNum, fileNum));
        PoiUtil.replaceTextInDocument(doc, "four", Integer.valueOf(specificationNum).toString());
        PoiUtil.replaceTextInDocument(doc, "five", getNum(specificationNum, total));
        PoiUtil.replaceTextInDocument(doc, "six", getResult("规范类"));
        PoiUtil.replaceTextInDocument(doc, "seven", Integer.valueOf(functionNum).toString());
        PoiUtil.replaceTextInDocument(doc, "eight", getNum(functionNum, total));
        PoiUtil.replaceTextInDocument(doc, "nine", getResult("功能类"));
        PoiUtil.replaceTextInDocument(doc, "ten", Integer.valueOf(performanceNum).toString());
        PoiUtil.replaceTextInDocument(doc, "eleven", getNum(performanceNum, total));
        PoiUtil.replaceTextInDocument(doc, "twelve", getResult("性能类"));
        PoiUtil.replaceTextInDocument(doc, "thirteen", Integer.valueOf(hiddenNum).toString());
        PoiUtil.replaceTextInDocument(doc, "hidden", getNum(hiddenNum, total));
        PoiUtil.replaceTextInDocument(doc, "fifteen", getResult("隐患类"));
        PoiUtil.replaceTextInDocument(doc, "question", getNum(fileFailNum, fileNum));
        PoiUtil.replaceTextInDocument(doc, "Test1", String.valueOf(LocalDate.now(zoneId).getYear()));
        PoiUtil.replaceTextInDocument(doc, "Test2", String.valueOf(LocalDate.now(zoneId).getMonthValue()));
        PoiUtil.replaceTextInDocument(doc, "Test3", String.valueOf(LocalDate.now(zoneId).getDayOfMonth()));
        //修改word模板中的表格数据
        List<XWPFTable> tables = doc.getTables();
        for (int i = 0; i < tables.size(); i++) {
            if (i == 0) continue;
            if (i == 1) {
                XWPFTable xwpfTable = tables.get(i);
                List<XWPFTableRow> rows = xwpfTable.getRows();
                for (int i1 = 0; i1 < rows.size(); i1++) {
                    if (i1 == 0) continue;
                    XWPFTableRow xwpfTableRow = rows.get(i1);
                    XWPFTableCell scanDescInfoCell = xwpfTableRow.getCell(1);
                    XWPFTableCell scanHandeCommentCell = xwpfTableRow.getCell(2);
                    if (i1 <= perfAndRiskFailTopTenList.size()) {
                        String scanItemId = perfAndRiskFailTopTenList.get(i1 - 1);
                        Optional<ScriptItemEntity> byId = scanItemRepository.findById(scanItemId);
                        String scanDescInfo = "";
                        if (byId.isPresent()) {
                            ScriptItemEntity scriptFileStatusEntity = byId.get();
                            scanDescInfo = scriptFileStatusEntity.getScanDescInfo();
                            scanDescInfoCell.setText(scanDescInfo);
                        }
                        Optional<ScriptItemEntity> byScanDescInfoAndDeletedFalse = scanItemRepository.findByScanDescInfoAndDeletedFalse(scanDescInfo);
                        if (byScanDescInfoAndDeletedFalse.isPresent()) {
                            ScriptItemEntity scriptFileStatusEntity = byScanDescInfoAndDeletedFalse.get();
                            String handComment = scriptFileStatusEntity.getHandComment();
                            scanHandeCommentCell.setText(handComment);
                        }
                    }
                }
            }
            if (i == 2) {
                XWPFTable xwpfTable = tables.get(i);
                if (xwpfTable != null) {
                    List<XWPFTableRow> rows = xwpfTable.getRows();
                    if (rows != null && !rows.isEmpty()) {
                        int rowIndex = 1; // 从第二行开始填充数据，假设第一行是标题行
                        //检查失败文件是否为空
                        if (!fileFailNumMap.isEmpty()) {
                            // 将 fileFailNumMap 按照值（失败次数）从大到小排序
                            List<Map.Entry<String, Integer>> sortedEntries = new ArrayList<>(fileFailNumMap.entrySet());
                            sortedEntries.sort((e1, e2) -> e2.getValue().compareTo(e1.getValue()));

                            // 遍历排序后的条目，并填充到表格中
                            for (Map.Entry<String, Integer> fileEntry : sortedEntries) {
                                if (rowIndex < rows.size() && rows.get(rowIndex) != null) {
                                    XWPFTableRow xwpfTableRow = rows.get(rowIndex);
                                    XWPFTableCell fileNameCell = xwpfTableRow.getCell(1);
                                    XWPFTableCell fileFailNumCell = xwpfTableRow.getCell(2);

                                    // 检查单元格是否为null，避免NullPointerException
                                    if (fileNameCell != null && fileFailNumCell != null) {
                                        fileNameCell.setText(fileEntry.getKey()); // 设置文件名
                                        fileFailNumCell.setText(String.valueOf(fileEntry.getValue())); // 设置失败次数
                                    }
                                    rowIndex++; // 移动到下一行
                                } else {
                                    break; // 如果行数超出范围或行为null，则停止填充
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 获取脚本失败扫描类别占总脚本数量
     *
     * @param type 扫描类别
     */
    private String getResult(String type) {
        Set<String> failedFileNames = new HashSet<>();
        for (Map.Entry<String, Map<String, Integer>> mapEntry : fileFailTypeMap.entrySet()) {
            String fileName = mapEntry.getKey();
            Map<String, Integer> failTypeNum = mapEntry.getValue();
            if (failTypeNum.containsKey(type) && failTypeNum.get(type) != 0) {
                failedFileNames.add(fileName);
            }
        }
        double count = failedFileNames.size();
        return String.format("%.1f", (count / fileNum) * 100);
    }

    private String getNum(int Num, Integer total) {
        if (Num == 0 || total.equals(0)) {
            return String.valueOf(0.0);
        }
        return String.format("%.2f", (double) Num / total * 100);
    }

    private void readFileMapContent(Map<String, File> fileObjectMap, List<ScriptItemEntity> scriptItemEntityList) {
        Map<String, List<ScriptPackScanResultEntity>> scanResultMap = new HashMap<>();
        for (Map.Entry<String, File> entry : fileObjectMap.entrySet()) {
            String fileName = entry.getKey();
            File file = entry.getValue();
            ScriptPackScanListEntity scriptPackScanListEntity = new ScriptPackScanListEntity();
            String absolutePath = file.getAbsolutePath();
            scriptPackScanListEntity.setScanFilePath(absolutePath);

            String sourceSql = cn.hutool.core.io.FileUtil.readUtf8String(absolutePath);

            // 初始化供word的扫描指标
            wordResult = initScript(scriptItemEntityList, fileName);
            // 扫描脚本
            String validatedSql = validateSql(sourceSql);
            // 获取扫描后word指标结果
            scanResultMap.put(sourceSql, new ArrayList<>(wordResult.values()));
            //文件失败次数统计
            getFileFailNum(validatedSql, fileName);
            //分类问题统计
            getFailTypeNum(validatedSql, fileName);
            //性能类隐患类统计
            getTypeFailMap(validatedSql,fileName,scanResultMap);
            //将扫描结果导出文件
            exportSqlScanResult(validatedSql, fileName);
        }
        //统计失败文件的个数
        getFailFileNum();
    }

    private List<String> getScanItem() {
        return new ArrayList<>(wordResult.keySet());
    }

    private void getTypeFailMap(String validatedSql, String fileName, Map<String, List<ScriptPackScanResultEntity>> scanResultMap) {
        Map<String, Integer> typeFailMap = fileFailTypeMap.get(fileName);
        for (Map.Entry<String, List<ScriptPackScanResultEntity>> entry : scanResultMap.entrySet()) {
            List<ScriptPackScanResultEntity> value = entry.getValue();
            for (ScriptPackScanResultEntity scriptPackScanResultEntity : value) {
                String scanState = scriptPackScanResultEntity.getScanState();
                String scanType = scriptPackScanResultEntity.getScanType();
                String scanListId = scriptPackScanResultEntity.getScanListId();
                if (scanState.equals("fail")) {
                    if (scanType.equals("性能类") || scanType.equals("隐患类")) {
                        perfAndRiskFailList.add(scanListId);
                    }
                }
            }
        }
        //不包含性能类隐患类直接跳过
        if (typeFailMap.containsKey("性能类")||typeFailMap.containsKey("隐患类")){
            for (String scanItemId : perfAndRiskFailList) {
                if (perfAndRiskFailMap.containsKey(scanItemId)){
                    perfAndRiskFailMap.put(scanItemId,perfAndRiskFailMap.get(scanItemId)+StringUtils.countMatches(validatedSql, scanItemId));
                }else {
                    perfAndRiskFailMap.put(scanItemId,StringUtils.countMatches(validatedSql, scanItemId));
                }
            }
        }
    }

    private void getFileFailNum(String validatedSql, String fileName) {
        String performance = "[性能]";
        String specification = "[规范]";
        String function = "[功能]";
        String hidden = "[隐患]";
        int performanceNum = StringUtils.countMatches(validatedSql, performance);
        int specificationNum = StringUtils.countMatches(validatedSql, specification);
        int functionNum = StringUtils.countMatches(validatedSql, function);
        int hiddenNum = StringUtils.countMatches(validatedSql, hidden);
        fileFailNumMap.put(fileName, performanceNum + specificationNum + functionNum + hiddenNum);
    }

    /**
     * @Method getFailTypeNum <br>
     * @Param validatedSql <br>
     * @Return void <br>
     * @Description <br> 统计脚本中各类问题
     * @Author ky.zhang<br>
     * @Date 2024/9/19 16:37 <br>
     * @Version V1.0<br>
     */
    private void getFailTypeNum(String validatedSql, String fileName) {
        String performance = "[性能]";
        String specification = "[规范]";
        String function = "[功能]";
        String hidden = "[隐患]";
        if(failTypeMap.containsKey("性能类")){
            failTypeMap.put("性能类",failTypeMap.get("性能类")+StringUtils.countMatches(validatedSql, performance));
        }else {
            failTypeMap.put("性能类", StringUtils.countMatches(validatedSql, performance));
        }
        if(failTypeMap.containsKey("规范类")){
            failTypeMap.put("规范类",failTypeMap.get("规范类")+StringUtils.countMatches(validatedSql, specification));
        }else {
            failTypeMap.put("规范类", StringUtils.countMatches(validatedSql, specification));
        }
        if(failTypeMap.containsKey("功能类")){
            failTypeMap.put("功能类",failTypeMap.get("功能类")+StringUtils.countMatches(validatedSql, function));
        }else {
            failTypeMap.put("功能类", StringUtils.countMatches(validatedSql, function));
        }
        if(failTypeMap.containsKey("隐患类")){
            failTypeMap.put("隐患类",failTypeMap.get("隐患类")+StringUtils.countMatches(validatedSql, hidden));
        }else {
            failTypeMap.put("隐患类", StringUtils.countMatches(validatedSql, hidden));
        }
        fileFailTypeMap.put(fileName, failTypeMap);
    }

    /**
     * @Method validateSql
     * @Param sql
     * @Return java.lang.String
     * @Description 通过listener方式检查脚本
     * @Author gj.xu
     * @Date 2024/9/13 18:30
     * @Version V2.0
     */
    private String validateSql(String sql) {
        try {
            // 假设你有一个SQL解析器，例如基于ANTLR实现的解析器
            GaussLexer lexer = new GaussLexer(CharStreams.fromString(sql));
            CommonTokenStream tokens = new CommonTokenStream(lexer);
            GaussParser parser = new GaussParser(tokens);
            // 载入规则
            parserListenerList.stream().forEach(l -> parser.addParseListener(l));
            // 开始解析，检查语法是否正确
            return SqlFormatterUtil.formatterSql(parser.sql_script().getText().replace("<EOF>", ""));

        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return "-- 解析失败\n" + StrUtil.format("/* \n{} \n*/", e.getMessage()) + StrUtil.format("/* \n{} \n*/", sql);
        }
    }

    private void exportSqlScanResult(String script, String fileName) {
        String filePath = sqlScanResultPath + "/" + fileName;
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            // 将扫描结果写入文件
            writer.write(script);
            // 刷新缓冲区，确保所有内容都写入文件
            writer.flush();
        } catch (IOException e) {
            throw new RuntimeException("扫描结果导出失败", e);
        }
    }

    private Map<String, ScriptPackScanResultEntity> initScript(List<ScriptItemEntity> scriptItemEntityList, String fileName) {
        List<ScriptPackScanResultEntity> scriptPackScanResultList;
        scriptPackScanResultList = turnToResult(scriptItemEntityList, fileName);
        return scriptPackScanResultList.stream()
                .map(s -> s.setScanState("success"))
                .map(s -> s.setScanTable("default"))
                .collect(Collectors.toMap(ScriptPackScanResultEntity::getScanItem, Function.identity()));
    }

    /**
     * @param scriptItemEntityList
     * @param fileName
     * @Method turnToResult
     * @Param
     * @Return java.util.List<com.hex.ds.code.detection.component.code.detection.model.ScriptPackScanResult>
     * @Description 模板对象维护为result对象
     * @Author Wang zhihao
     * @Date 2023/10/8 13:35
     * @Version V1.0
     */
    private List<ScriptPackScanResultEntity> turnToResult(List<ScriptItemEntity> scriptItemEntityList, String fileName) {
        List<ScriptPackScanResultEntity> scriptPackScanResults = new ArrayList<>();
        for (ScriptItemEntity scriptPackScanItem : scriptItemEntityList) {
            ScriptPackScanResultEntity scriptPackScanResult = new ScriptPackScanResultEntity()
                    .setScanListId(scriptPackScanItem.getId())
                    .setScanName(scriptPackScanItem.getScanName())
                    .setFileName(fileName)
                    .setScanType(scriptPackScanItem.getScanType())
                    .setScanLevel(scriptPackScanItem.getScanLevel())
                    .setScanItem(scriptPackScanItem.getScanName())
                    .setScanItemDescribe(scriptPackScanItem.getScanDesc())
                    .setScanDescInfo(scriptPackScanItem.getScanDescInfo());
            scriptPackScanResults.add(scriptPackScanResult);
        }
        return scriptPackScanResults;
    }

    private List<ScriptItemEntity> readScanExcel(String scanExcelPath) {
        List<ScriptItemEntity> list = new ArrayList<>();
        FileInputStream excelFile = null;
        Workbook workbook = null;
        try {
            excelFile = new FileInputStream(scanExcelPath);
            workbook = WorkbookFactory.create(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);

            for (Row currentRow : datatypeSheet) {
                if (currentRow.getRowNum() == 0 || "N".equals(currentRow.getCell(4).getStringCellValue())) {
                    continue;
                }
                ScriptItemEntity scriptItem = new ScriptItemEntity();
                scriptItem.setId(currentRow.getCell(0).getStringCellValue());
                scriptItem.setScanName(currentRow.getCell(1).getStringCellValue());
                scriptItem.setScanDesc(currentRow.getCell(2).getStringCellValue());
                scriptItem.setScanLevel(currentRow.getCell(3).getStringCellValue());
                scriptItem.setIsScan(currentRow.getCell(4).getStringCellValue());
                scriptItem.setScanDescInfo(currentRow.getCell(5).getStringCellValue());
                scriptItem.setScanType(currentRow.getCell(6).getStringCellValue());
                scriptItem.setHandComment(currentRow.getCell(7).getStringCellValue());
                scanItemRepository.save(scriptItem);
                list.add(scriptItem);
            }
        } catch (IOException e) {
            throw new RuntimeException("扫描项读取失败", e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (excelFile != null) {
                try {
                    excelFile.close();
                } catch (IOException e) {
                    log.error("文件操作失败");
                }
            }
        }
        return list;
    }

    private Integer countDirectory(String filePaths) {
        File directory = new File(filePaths);
        int count = 0;
        File[] files = directory.listFiles();

        if (files != null) {
            for (File file : files) {
                if (file.isFile()) {
                    count++;
                }
            }
        }
        return count;
    }

    private Map<String, File> createFileObjectMap(Map<String, String> filePathMap) {
        Map<String, File> fileObjectMap = new HashMap<>();

        for (Map.Entry<String, String> entry : filePathMap.entrySet()) {
            String fileName = entry.getKey();
            String filePath = entry.getValue();
            File file = new File(filePath);
            fileObjectMap.put(fileName, file);
        }

        return fileObjectMap;
    }
}
